﻿using SD.ContentSystem.Domain.Entities;
using SD.ContentSystem.Domain.IRepositories.Interfaces;
using SD.Infrastructure.Repository.EntityFramework;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;

namespace SD.ContentSystem.Repository.Implements
{
    /// <summary>
    /// 文章仓储实现
    /// </summary>
    public class ArticleRepository : EFAggRootRepositoryProvider<Article>, IArticleRepository
    {
        #region # 根据条件获取实体对象集合（默认排序） —— override IOrderedQueryable<Article> FindBySort(...
        /// <summary>
        /// 根据条件获取实体对象集合（默认排序）
        /// </summary>
        /// <param name="condition">条件表达式</param>
        /// <returns></returns>
        protected override IOrderedQueryable<Article> FindBySort(Expression<Func<Article, bool>> condition)
        {
            return base.Find(condition).OrderByDescending(x => x.IsTop).ThenByDescending(x => x.Sort).ThenByDescending(x => x.VisitCount).ThenByDescending(x => x.AddedTime);
        }
        #endregion

        #region # 根据栏目获取文章列表 —— IEnumerable<Article> FindByChannel(string channelNo)
        /// <summary>
        /// 根据栏目获取文章列表
        /// </summary>
        /// <param name="channelNo">栏目编号</param>
        /// <returns>文章列表</returns>
        public IEnumerable<Article> FindByChannel(string channelNo)
        {
            return this.FindBySort(x => x.ChannelNo == channelNo).AsEnumerable();
        }
        #endregion

        #region # 分页获取文章列表 —— IEnumerable<Article> FindByPage(string keywords...
        /// <summary>
        /// 分页获取文章列表
        /// </summary>
        /// <param name="keywords">关键字</param>
        /// <param name="channelNo">栏目编号</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">页容量</param>
        /// <param name="rowCount">总记录条数</param>
        /// <param name="pageCount">总页数</param>
        /// <returns>文章列表</returns>
        public IEnumerable<Article> FindByPage(string keywords, string channelNo, DateTime? startTime, DateTime? endTime, int pageIndex, int pageSize, out int rowCount, out int pageCount)
        {
            Expression<Func<Article, bool>> condition =
                x =>
                    (string.IsNullOrEmpty(keywords) || x.Keywords.Contains(keywords)) &&
                    (string.IsNullOrEmpty(channelNo) || x.ChannelNo == channelNo) &&
                    (startTime == null || x.AddedTime >= startTime) &&
                    (endTime == null || x.AddedTime <= endTime);

            return this.FindByPage(condition, pageIndex, pageSize, out rowCount, out pageCount).AsEnumerable();
        }
        #endregion

        #region # 获取文章索引 —— int GetRowIndex(string channelNo, Guid articleId)
        /// <summary>
        /// 获取文章索引
        /// </summary>
        /// <param name="articleId">文章Id</param>
        /// <param name="channelNo">栏目编号</param>
        /// <returns>文章索引</returns>
        public int GetRowIndex(string channelNo, Guid articleId)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT RowIndex FROM ");
            sqlBuilder.Append("( ");
            sqlBuilder.Append("SELECT ");
            sqlBuilder.Append("ROW_NUMBER() OVER (ORDER BY IsTop DESC, VisitCount DESC, Sort DESC, AddedTime DESC) AS RowIndex, ");
            sqlBuilder.AppendFormat("* FROM dbo.Article  WHERE ChannelNo = {0} ", channelNo);
            sqlBuilder.Append(") AS T ");
            sqlBuilder.AppendFormat("WHERE T.Id = {0} ", articleId);

            int rowIndex = this.ExecuteSqlQuery<int>(sqlBuilder.ToString()).First();
            return rowIndex;
        }
        #endregion

        #region # 获取上一篇文章 —— Article GetPrevArticle(string channelNo, Guid articleId)
        /// <summary>
        /// 获取上一篇文章
        /// </summary>
        /// <param name="channelNo">栏目编号</param>
        /// <param name="articleId">文章Id</param>
        /// <returns>上一篇文章</returns>
        public Article GetPrevArticle(string channelNo, Guid articleId)
        {
            int rowIndex = this.GetRowIndex(channelNo, articleId);

            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT TOP 1 * FROM ");
            sqlBuilder.Append("( ");
            sqlBuilder.Append("SELECT ");
            sqlBuilder.Append("ROW_NUMBER() OVER (ORDER BY IsTop DESC, VisitCount DESC, Sort DESC, AddedTime DESC) AS RowIndex, ");
            sqlBuilder.Append("* FROM dbo.Article ");
            sqlBuilder.AppendFormat("WHERE ChannelNo = {0} ", channelNo);
            sqlBuilder.Append(") AS T ");
            sqlBuilder.AppendFormat("WHERE T.RowIndex < {0} ORDER BY RowIndex DESC ", rowIndex);

            Article[] articles = this.ExecuteSqlQuery<Article>(sqlBuilder.ToString()).ToArray();

            if (articles.Any())
            {
                return articles.First();
            }

            return null;
        }
        #endregion

        #region # 获取下一篇文章 —— Article GetNextArticle(string channelNo, Guid articleId)
        /// <summary>
        /// 获取下一篇文章
        /// </summary>
        /// <param name="channelNo">栏目编号</param>
        /// <param name="articleId">文章Id</param>
        /// <returns>下一篇文章</returns>
        public Article GetNextArticle(string channelNo, Guid articleId)
        {
            int rowIndex = this.GetRowIndex(channelNo, articleId);

            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("SELECT TOP 1 * FROM ");
            sqlBuilder.Append("( ");
            sqlBuilder.Append("SELECT ");
            sqlBuilder.Append("ROW_NUMBER() OVER (ORDER BY IsTop DESC, VisitCount DESC, Sort DESC, AddedTime DESC) AS RowIndex, ");
            sqlBuilder.Append("* FROM dbo.Article ");
            sqlBuilder.AppendFormat("WHERE ChannelNo = {0} ", channelNo);
            sqlBuilder.Append(") AS T ");
            sqlBuilder.AppendFormat("WHERE T.RowIndex > {0} ORDER BY RowIndex DESC ", rowIndex);

            Article[] articles = this.ExecuteSqlQuery<Article>(sqlBuilder.ToString()).ToArray();

            if (articles.Any())
            {
                return articles.First();
            }

            return null;
        }
        #endregion

        #region # 获取给定数量的文章列表 —— IEnumerable<Article> Find(string channelNo...
        /// <summary>
        /// 获取给定数量的文章列表
        /// </summary>
        /// <param name="channelNo">栏目编号</param>
        /// <param name="takeCount">提取数量</param>
        /// <returns>文章列表</returns>
        public IEnumerable<Article> Find(string channelNo, int takeCount)
        {
            IQueryable<Article> specArticles = this.Find(x => x.ChannelNo == channelNo);

            return specArticles.Take(takeCount).AsEnumerable();
        }
        #endregion

        #region # 是否存在文章 —— bool ExistsByChannel(string channelNo)
        /// <summary>
        /// 是否存在文章
        /// </summary>
        /// <param name="channelNo">栏目编号</param>
        /// <returns>是否存在</returns>
        public bool ExistsByChannel(string channelNo)
        {
            return this.Exists(x => x.ChannelNo == channelNo);
        }
        #endregion

        #region # 根据栏目获取文章记录数 —— int Count(string channelNo)
        /// <summary>
        /// 根据栏目获取文章记录数
        /// </summary>
        /// <param name="channelNo">栏目编号</param>
        /// <returns>记录数</returns>
        public int Count(string channelNo)
        {
            return this.Count(x => x.ChannelNo == channelNo);
        }
        #endregion
    }
}
